package com.css.fxfzypg.modules.rating.library.dao;

import com.css.fxfzypg.modules.rating.library.model.entity.KpExamScoringDbIndex2;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

@Slf4j
@Repository
public class KpExamScoringDbIndex2Repository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public void batchSave(List<KpExamScoringDbIndex2> index2List) {
        String sql = "INSERT INTO kp_exam_scoring_db_index2(id, name, kpesdi1_id, create_time) values(:id, :name, :kpesdi1Id, :createTime)";
//        String sql = "INSERT INTO kp_exam_scoring_db_index2(id, name, score, kpesdi1_id, create_time) values(:id, :name, :score, :kpesdi1Id, :createTime)";
        namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(index2List.toArray()));
    }

    public void updateById(KpExamScoringDbIndex2 kpExamScoringDbIndex2) {
        StringBuilder sql = new StringBuilder("update kp_exam_scoring_db_index2 set ");
        if (ObjectUtils.isNotEmpty(kpExamScoringDbIndex2.getName())) {
            sql.append(" name = '").append(kpExamScoringDbIndex2.getName()).append("', ");
        }
//        if (ObjectUtils.isNotEmpty(kpExamScoringDbIndex2.getScore())) {
//            sql.append(" score = '").append(kpExamScoringDbIndex2.getScore()).append("', ");
//        }
        sql.deleteCharAt(sql.lastIndexOf(", "));
        sql.append(" where id = '").append(kpExamScoringDbIndex2.getId()).append("' ");
        jdbcTemplate.update(sql.toString());
    }

    public void insert(KpExamScoringDbIndex2 kpExamScoringDbIndex2) {
//        String sql = "INSERT INTO kp_exam_scoring_db_index2(id, name, score, kpesdi1_id, create_time) values(:id, :name, :score, :kpesdi1Id, :createTime)";
        String sql = "INSERT INTO kp_exam_scoring_db_index2(id, name, kpesdi1_id, create_time) values(:id, :name, :kpesdi1Id, :createTime)";
        namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(kpExamScoringDbIndex2));
    }

    public KpExamScoringDbIndex2 getById(String id) {
        try {
            String sql = "select * from kp_exam_scoring_db_index2 where id = '" + id + "'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(KpExamScoringDbIndex2.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public void deleteById(String id) {
        String sql = "delete from kp_exam_scoring_db_index2 where id = '" + id + "'";
        jdbcTemplate.update(sql);
    }

    public int countByKpesdi1Id(String kpesdi1Id) {
        String sql = "select count(*) from kp_exam_scoring_db_index2 where kpesdi1_id = '" + kpesdi1Id + "'";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    public List<KpExamScoringDbIndex2> list(KpExamScoringDbIndex2 index2Param) {
        StringBuilder sql = new StringBuilder("select * from kp_exam_scoring_db_index2 where 1=1");
        if (ObjectUtils.isNotEmpty(index2Param.getId())) {
            sql.append(" and id = '").append(index2Param.getId()).append("' ");
        }
        if (ObjectUtils.isNotEmpty(index2Param.getName())) {
            sql.append(" and name = '").append(index2Param.getName()).append("' ");
        }
//        if (ObjectUtils.isNotEmpty(index2Param.getScore())) {
//            sql.append(" and score = '").append(index2Param.getScore()).append("' ");
//        }
        if (ObjectUtils.isNotEmpty(index2Param.getKpesdi1Id())) {
            sql.append(" and kpesdi1_id = '").append(index2Param.getKpesdi1Id()).append("' ");
        }
        sql.append(" order by create_time asc");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(KpExamScoringDbIndex2.class));
    }

    /**
     *
     * @param kesdId 考评库id
     * @return
     */
    public List<KpExamScoringDbIndex2> getByKesdId(String kesdId) {
        String sql = "select t1.* from kp_exam_scoring_db_index2 t1 left join kp_exam_scoring_db_index1 t2 on t1.kpesdi1_id = t2.id left join kp_exam_scoring_db t3 on t3.id = t2.kesd_id where t3.id = '" + kesdId + "' order by t1.create_time asc";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpExamScoringDbIndex2.class));
    }

//    public BigDecimal sumScoreByKesdi1Id(String index1Id) {
//        return jdbcTemplate.queryForObject("select sum(score) from kp_exam_scoring_db_index2 where kpesdi1_id = '" + index1Id + "'", BigDecimal.class);
//    }
}
